package com.plantplaces.dao;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.plantplaces.dto.Plant;
import com.plantplaces.dto.PlantSearch;

/**
 * offline data interactions for dealing with SQLite
 * 
 * @author jonesbr
 *
 */
public class OfflinePlantDAO extends SQLiteOpenHelper implements IOfflinePlantDAO {

	private static final String RECOMMENDED = "RECOMMENDED";
	private static final String NATIVE_PLANT = "NATIVE";
	private static final String EDIBLE = "EDIBLE";
	private static final String COMMON = "COMMON";
	private static final String CULTIVAR = "CULTIVAR";
	private static final String SPECIES = "SPECIES";
	private static final String GENUS = "GENUS";
	private static final String GUID = "GUID";
	private static final String PLANT_SQLITE_ID = "_id";
	private static final String PLANTS_TABLE = "plants";
	private String columns;

	public OfflinePlantDAO (Context ctx) {
		super(ctx, "plantplaces", null, 1);
		// initialize our standard set of columns.
		columns = GUID + ", " + GENUS + ", " + SPECIES + ", " + CULTIVAR + ", " + COMMON;
	}
	
	@Override
	public List<Plant> fetchPlants(PlantSearch ps) throws Exception {
		// Declare our return type.
		List<Plant> allPlants = new ArrayList<Plant>();
		
		// find the search term.
		String searchTerm = ps.getCommon();
		
		// perform a fuzzy search across several columns.
		String sqlSelect = "select " + columns + " from " + PLANTS_TABLE + " where " + COMMON + " like '% " + searchTerm + "%' OR " + GENUS + " like '%" + searchTerm +"%' OR " + SPECIES + " like '%" + searchTerm + "%'";

		// run the query.
		Cursor cursor = getReadableDatabase().rawQuery(sqlSelect, null);
		
		if (cursor.getCount() > 0) {
			
			// we have results.
			cursor.moveToFirst();
			
			// iterate over the results until we have the last.
			while(!cursor.isAfterLast()) {
				// declare a plant to hold results.
				Plant plant = new Plant();
				
				// populate the plant from the cursor.
				populatePlantFromCursor(plant, cursor);
				
				// add the plant to the return collection.
				allPlants.add(plant);
				
				// move to the next result.
				cursor.moveToNext();
			}
		}
		
		return allPlants;
	}
	
	private Plant fetchByPlantPlacesID(int id) {
		
		String sqlSelect = "select " + columns + " from " + PLANTS_TABLE + " where " + GUID + " = " + id;
		
		Plant plant = null;
		
		// run this query.
		Cursor cursor = getReadableDatabase().rawQuery(sqlSelect, null);
		
		if (cursor.getCount() == 1) {
			cursor.moveToFirst();
			
			plant = new Plant();
			
			populatePlantFromCursor(plant, cursor);
			
			cursor.close();
		}
		
		return plant;
	}

	private void populatePlantFromCursor(Plant plant, Cursor cursor) {
		plant.setId(cursor.getInt(cursor.getColumnIndex(GUID)));
		plant.setGenus(cursor.getString(1));
		plant.setSpecies(cursor.getString(2));
		plant.setCultivar(cursor.getString(3));
		plant.setCommon(cursor.getString(4));
	}

	
	@Override
	public void insert(List<Plant> allPlants) throws Exception {
		// iterate over all plants.
		for (Plant plant : allPlants) {
			
			// see if this plant already exists.
			Plant thisPlant = fetchByPlantPlacesID(plant.getId());
			// if we don't have this plant, it is null.  Let's insert it.
			if(thisPlant == null) {
			
				ContentValues cv = new ContentValues();
				cv.put(GENUS, plant.getGenus());
				cv.put(SPECIES, plant.getSpecies());
				cv.put(CULTIVAR, plant.getCultivar());
				cv.put(COMMON, plant.getCommon());
				cv.put(GUID, plant.getId());
				// insert the data
				long sqliteId = getWritableDatabase().insert(PLANTS_TABLE, GENUS, cv);
			}
		}
		
		
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		String schema = "CREATE TABLE " + PLANTS_TABLE + " (" + PLANT_SQLITE_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + GUID + " INTEGER, " + GENUS + " TEXT, " + SPECIES + " TEXT, "+CULTIVAR+" TEXT, "+COMMON+" TEXT, "+EDIBLE+" INTEGER, "+NATIVE_PLANT+" INTEGER, "+RECOMMENDED+" INTEGER );";
		schema.length();
		db.execSQL(schema);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		
	}

	/**
	 * Return a list of all unique genus, species, common in the database
	 * @return
	 */
	@Override
	public List<String> fetchPlantNames() throws Exception {
	
		// Return value.
		List<String> allNames = new ArrayList<String>();

		// define the queries that we will run.
		String genus = "SELECT DISTINCT " + GENUS + " FROM PLANTS";
		String species = "SELECT DISTINCT " + SPECIES + " FROM PLANTS";
		String common = "SELECT DISTINCT " + COMMON + " FROM PLANTS";
		
		// run these queries
		populateNamesFromSQL(allNames, genus);
		populateNamesFromSQL(allNames, species);
		populateNamesFromSQL(allNames, common);
		
		return allNames;
	}

	private void populateNamesFromSQL(List<String> allNames, String sql) {
		// run the query.
		Cursor cursor = getReadableDatabase().rawQuery(sql, null);
		
		// if we have results, populate allNames with those results.
		if (cursor.getCount() > 0) {
			
			// we have results.
			cursor.moveToFirst();
			
			// iterate over the results until we have the last.
			while(!cursor.isAfterLast()) {
				// declare a plant to hold results.
				allNames.add(cursor.getString(0));
				// move to the next result.
				cursor.moveToNext();
			}
		}
	}
	
}
